﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Web;

namespace Botwave.Common
{
    /// <summary>
    /// Excel导入导出类
    /// </summary>
    /// <remarks>
    /// 只能导入导出xls文件，需要添加NPOI组件引用
    /// </remarks>
    /// <example>
    ///     <![CDATA[
    ///         NPOIUtils nu=new NPOIUtils();
    ///         nu.SetRowHeidth(400);//可以忽略,使用默认高度请设置为0，或不设置
    ///         nu.AddColumn("new1","列1",2000,true);//导出必须设置，导入不用设置,如果使用默认宽度请设置为0
    ///         nu.AddColumn("new2","列2",2000,false);
    ///         nu.SetDocumentInfo(new ExcelInfo(...));//设置文件属性，可以忽略
    ///         
    ///         nu.ExportToWeb(dtSource,"book1.xls");//直接输出到页面
    ///         nu.ExportToFile(dtSource, Server.MapPath("~/"+"text.xls"));//B/S保存到服务器或C/S保存到本机
    ///         DataTable dt = nu.Import(Server.MapPath("~/" + "text.xls"));//导入到DataTable，输入绝对路径
    ///     ]]>
    /// </example>
    public class ExcelUtils
    {
        #region 属性
        /// <summary>
        /// 默认表头
        /// </summary>
        private string SheetName = "Sheet";

        /// <summary>
        /// 列头即表头
        /// </summary>
        public List<Column> Columns=new List<Column>();

        /// <summary>
        /// 行高
        /// </summary>
        public short RowHeidth;

        /// <summary>
        /// Excel文件属性
        /// </summary>
        public ExcelInfo DocumentInfo;

        /// <summary>
        /// Excel对象
        /// </summary>
        public IWorkbook workbook;

        

        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelUtils()
        {

        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="columns">列信息</param>
        /// <param name="rowWidth">行高</param>
        public ExcelUtils(List<Column> columns, short rowHeight)
        {
            this.Columns = columns;
            this.RowHeidth = rowHeight;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="columns">列信息</param>
        /// <param name="rowWidth">行高</param>
        /// <param name="documentinfo">文档属性</param>
        public ExcelUtils(List<Column> columns, short rowHeight, ExcelInfo documentinfo)
        {
            this.Columns = columns;
            this.RowHeidth = rowHeight;
            this.DocumentInfo = documentinfo;
        }
        #endregion

        #region 设置表格信息
        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="rowHeight"></param>
        public void SetRowHeidth(short rowHeight)
        {
            this.RowHeidth = rowHeight;
        }

        /// <summary>
        /// 设置列组
        /// </summary>
        /// <param name="columns"></param>
        public void SetColumns(List<Column> columns)
        {
            this.Columns = columns;
        }

        /// <summary>
        /// 设置文件属性
        /// </summary>
        /// <param name="documentInfo"></param>
        public void SetDocumentInfo(ExcelInfo documentInfo)
        {
            this.DocumentInfo = documentInfo;
        }

        /// <summary>
        /// 添加到已有列组
        /// </summary>
        /// <param name="columnKey">键值</param>
        /// <param name="columnName">Excel表头</param>
        /// <param name="width">列宽度</param>
        /// <param name="wrapText">是否自动换行</param>
        public void AddColumn(string columnKey, string columnName, int width, bool wrapText)
        {
            Columns.Add(new Column { ColumnKey = columnKey, ColumnName = columnName, Width = width, WrapText = wrapText });
        }

        /// <summary>
        /// 添加到已有列组
        /// </summary>
        /// <param name="column">列</param>
        public void AddColumn(Column column)
        {
            Columns.Add(column);
        }

        /// <summary>
        /// 添加到已有列组
        /// </summary>
        /// <param name="columns">列组</param>
        public void AddColumns(List<Column> columns)
        {
            Columns.AddRange(columns);
        }

        #endregion

        #region 导出
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <returns>内存流</returns>
        public MemoryStream Export(DataTable dtSource)
        {
            if (Columns == null || Columns.Count == 0)
                throw (new Exception("请对Columns设置要导出的列名！"));

            IWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);

            MemoryStream ms = new MemoryStream();
            SaveExcelFile(excelWorkbook, ms);
            return ms;
        }

        /// <summary>
        /// DataTable导出到Excel文件，b/s保存到服务器，c/s则保存到本机
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="filePath">保存路径（绝对路径）</param>
        public void ExportToFile(DataTable dtSource, string filePath)
        {
            if (Columns == null || Columns.Count == 0)
                throw (new Exception("请对Columns设置要导出的列名！"));

            IWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(dtSource, excelWorkbook);
            SaveExcelFile(excelWorkbook, filePath);
        }

        /// <summary>
        /// Web导出，直接输出下载
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="fileName">文件名称</param>
        public void ExportToWeb(DataTable dtSource,string fileName)
        {
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.BinaryWrite(Export(dtSource).GetBuffer());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 将WebControl数据列表导出至Excel
        /// </summary>
        /// <param name="objControl">控件对象</param>
        /// <param name="fileName">文件名称</param>
        public static void ExportToWeb(System.Web.UI.Control objControl, string fileName)
        {
            fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);

            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "UTF-8";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            objControl.RenderControl(oHtmlTextWriter);

            objControl = null;
            System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.End();
        }

        #endregion

        #region 导入
        /// <summary>读取excel
        /// 默认第一行为标头，第一个Sheet
        /// </summary>
        /// <param name="strFileName">excel文档路径（绝对路径）</param>
        /// <returns>DataTable</returns>
        public DataTable Import(string filePath)
        {
            DataTable dt = new DataTable();

            IWorkbook IWorkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook = new HSSFWorkbook(file);
            }

            ISheet sheet = IWorkbook.GetSheetAt(0);

            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径（绝对路径）</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns>DataTable</returns>
        public DataTable Import(string filePath,string sheetName)
        {
            
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook IWorkbook = new HSSFWorkbook(file);
                ISheet sheet = IWorkbook.GetSheet(sheetName);
                DataTable dt = new DataTable();
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = headerRow.GetCell(j);
                    dt.Columns.Add(cell.ToString());
                }

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }

                    dt.Rows.Add(dataRow);
                }
                return dt;
            }
            
            
        }
        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="filePath">文档路径（绝对路径）</param>
        /// <param name="SheetName">sheet名称</param>
        /// <param name="HeaderRowIndex">表头所在行</param>
        /// <returns>DataTable</returns>
        public DataTable Import(string filePath, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                ISheet sheet = workbook.GetSheet(SheetName);

                DataTable table = new DataTable();

                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                return table;
            }
        }
        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="filePath">（绝对路径）</param>
        /// <param name="SheetIndex">第几个sheet</param>
        /// <param name="HeaderRowIndex">标题所在行</param>
        /// <returns></returns>
        public DataTable Import(string filePath, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                ISheet sheet = workbook.GetSheetAt(SheetIndex);

                DataTable table = new DataTable();

                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }

                    table.Rows.Add(dataRow);
                }
                workbook = null;
                sheet = null;
                return table;
            }
        }

        #endregion

        #region 私有方法
        /// <summary>
        /// 创建Excel对象
        /// </summary>
        /// <param name="filePath"></param>
        private IWorkbook CreateExcelFile()
        {
            IWorkbook workbook = new HSSFWorkbook();
            this.SetSummaryInformation(workbook);
            return workbook;
        }

        /// <summary>
        /// 创建Excel对象
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <returns></returns>
        private IWorkbook CreateExcelFile(Stream excelFileStream)
        {
            IWorkbook workbook = new HSSFWorkbook(excelFileStream);
            return workbook;
        }
        /// <summary>
        /// 创建Excel对象
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public IWorkbook CreateExcelFils(string filePath)
        {
            int idx = filePath.LastIndexOf(".");
            string fixName = filePath.Substring(idx + 1);
            IWorkbook workbook;
            if (this.IsExcelFile(filePath))
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Open))
                {
                    if (fixName.ToLower() == "xls")
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                }
            }
            else
            {
                throw new Exception("输入的不是Excle文档路径");
            }
            return workbook;
        }

        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        private void CreateHeader(ISheet excelSheet)
        {
            int cellIndex = 0;
            IRow newRow = excelSheet.CreateRow(0);
            //循环导出列
            foreach (var de in Columns)
            {
                if (de.Width > 0)
                {
                    excelSheet.SetColumnWidth(cellIndex, de.Width);
                }
                ICell newCell = newRow.CreateCell(cellIndex);
                newCell.SetCellValue(de.ColumnName);
                cellIndex++;
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        private void InsertRow(DataTable dtSource, IWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            ISheet newsheet = null;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet(SheetName + sheetCount);
            CreateHeader(newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出10000条数据 创建新的工作簿
                if (rowCount == 10000)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(newsheet);
                }

                IRow newRow = newsheet.CreateRow(rowCount);
                if (RowHeidth != 0) {
                    newRow.Height = this.RowHeidth;
                }
                InsertCell(dr, newRow, newsheet, excelWorkbook);
            }
        }

        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        private void InsertCell(DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook)
        {
            int cellIndex = 0;
            foreach(var column in Columns)
            {
                //列名称
                string columnsName = column.ColumnKey;
                ICell newCell = null;
                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        //设置换行
                        if (column.WrapText)
                        {
                            ICellStyle cs = excelWorkBook.CreateCellStyle();
                            cs.WrapText = true;
                            newCell.CellStyle = cs;
                        }
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        IDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
                ++cellIndex;
            }
        }

        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        private void SaveExcelFile(IWorkbook excelWorkBook, string filePath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(filePath, FileMode.Create);
                excelWorkBook.Write(file);
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                }
            }
        }

        /// <summary>
        /// 保存Excel到流中
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        private void SaveExcelFile(IWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {

            }
        }

        /// <summary>
        /// 文件属性信息
        /// </summary>
        /// <param name="workbook"></param>
        private void SetSummaryInformation(IWorkbook workbook)
        {
            
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = DocumentInfo.Company;
            (workbook as HSSFWorkbook).DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = DocumentInfo.Author; //填加xls文件作者信息
            si.ApplicationName = DocumentInfo.ApplicationName; //填加xls文件创建程序信息
            si.LastAuthor = DocumentInfo.LastAuthor; //填加xls文件最后保存者信息
            si.Comments = DocumentInfo.Comments; //填加xls文件作者信息
            si.Title = DocumentInfo.Title; //填加xls文件标题信息
            si.Subject = DocumentInfo.Subject;//填加文件主题信息
            si.CreateDateTime = DateTime.Now;
            (workbook as HSSFWorkbook).SummaryInformation = si;
        }

        /// <summary>
        /// 判断是否为excel文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private bool IsExcelFile(string fileName)
        {
            int idx = fileName.LastIndexOf(".");
            string fixName = fileName.Substring(idx + 1);
            return (fixName.ToLower() == "xls" || fixName.ToLower()=="xlsx");
        }

        /// <summary>
        /// 获取excel的sheet名.
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public List<string> ReadExcelSheetNames(IWorkbook workbook)
        {
            List<string> SheetNames = new List<string>();
            int SheetIndex=0;
            while(true){
                try { 
                    SheetNames.Add(workbook.GetSheetName(SheetIndex));
                    SheetIndex++;
                }
                catch{
                    break;
                }
            }
            return SheetNames;
        }
        #endregion

        #region 静态方法
        #endregion

        #region 实体
        /// <summary>
        /// 列实体
        /// </summary>
        public class Column {
            /// <summary>
            /// 列名(实体或DataTable列名)
            /// </summary>
            public string ColumnKey { get; set; }
            /// <summary>
            /// 列名(Excel表头)
            /// </summary>
            public string ColumnName { get; set; }
            /// <summary>
            /// 列的宽度
            /// </summary>
            public int Width { get; set; }
            /// <summary>
            /// 是否自动换行
            /// </summary>
            public bool WrapText { get; set; }
        }

        /// <summary>
        /// Excel文档属性
        /// </summary>
        public class ExcelInfo {
            public ExcelInfo()
            {
                this.Company = "";
                this.Author = "";
                this.ApplicationName = "";
                this.LastAuthor = "";
                this.Comments = "";
                this.Title = "";
                this.Subject = "";
                this.CreateDateTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            /// <summary>
            /// 公司信息
            /// </summary>
            public string Company { get; set; }
            /// <summary>
            /// 文件作者
            /// </summary>
            public string Author { get; set; }
            /// <summary>
            /// 创建程序
            /// </summary>
            public string ApplicationName { get; set; }
            /// <summary>
            /// 文件最后保存者
            /// </summary>
            public string LastAuthor { get; set; }
            /// <summary>
            /// 内容说明
            /// </summary>
            public string Comments { get; set; }
            /// <summary>
            /// 文件标题
            /// </summary>
            public string Title { get; set; }
            /// <summary>
            /// 文件主题
            /// </summary>
            public string Subject { get; set; }
            /// <summary>
            /// 创建时间
            /// </summary>
            public string CreateDateTime { get; set; }
        }
        #endregion
    }
}
